package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOusers;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOuser;

public class DAOusersMySQL implements DAOusers
{
	private DBManager dbManager;
	
	public DAOusersMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertUser(VOuser vOuser) 
	{
		int code = -1;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try
		{
			preparedStatement = connection.prepareStatement("INSERT INTO users (firstName,lastName,identification,address,city,telephone,userName,password,userType) VALUES (?,?,?,?,?,?,?,?,?)");

			preparedStatement.setString(1, vOuser.getFirstName());
			preparedStatement.setString(2, vOuser.getLastName());
			preparedStatement.setInt(3, vOuser.getIdentification());
			preparedStatement.setString(4, vOuser.getAddress());
			preparedStatement.setInt(5, vOuser.getCity());
			preparedStatement.setString(6, vOuser.getTelephone());
			preparedStatement.setString(7, vOuser.getUserName());
			preparedStatement.setString(8, vOuser.getPassword());
			preparedStatement.setInt(9, vOuser.getUserType());

			preparedStatement.execute();

			preparedStatement = connection.prepareStatement("SELECT code FROM users ORDER BY code DESC LIMIT 1");

			resultSet = preparedStatement.executeQuery();

			resultSet.next();

			code = resultSet.getInt(1);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return code;
	}
	
	public boolean updateUser(VOuser vOuser)
	{
		PreparedStatement preparedStatement = null;
		boolean success = true;
		Connection connection = dbManager.getConnection();
		
		try
		{
			preparedStatement = connection.prepareStatement("UPDATE users SET firstName=?,lastName=?,identification=?,address=?,city=?,telephone=?,userName=?,password=?,userType=? WHERE code=?");
			
			preparedStatement.setString(1, vOuser.getFirstName());
			preparedStatement.setString(2, vOuser.getLastName());
			preparedStatement.setInt(3, vOuser.getIdentification());
			preparedStatement.setString(4, vOuser.getAddress());
			preparedStatement.setInt(5, vOuser.getCity());
			preparedStatement.setString(6, vOuser.getTelephone());
			preparedStatement.setString(7, vOuser.getUserName());
			preparedStatement.setString(8, vOuser.getPassword());
			preparedStatement.setInt(9, vOuser.getUserType());
			preparedStatement.setInt(10, vOuser.getCode());
			
			preparedStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			success = false;
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return success;
	}

	public boolean removeUser(VOuser vOuser) 
	{
		PreparedStatement preparedStatement = null;
		boolean success = true;
		Connection connection = dbManager.getConnection();
		
		try
		{
			preparedStatement = connection.prepareStatement("UPDATE users SET active=? WHERE userName=?");
			
			preparedStatement.setBoolean(1, false);
			preparedStatement.setString(2, vOuser.getUserName());
			
			preparedStatement.execute();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			success = false;
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return success;
	}
	
	public ArrayList<VOuser> listUsers() 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<VOuser> result = null;

		Connection connection = dbManager.getConnection();
		try
		{
			preparedStatement = connection.prepareStatement( "SELECT code,firstName,lastName,identification,address,city,telephone,userName,userType FROM users" );

			resultSet = preparedStatement.executeQuery();

			result = new ArrayList<VOuser>();

			while( resultSet.next() )
			{
				VOuser vOuser = new VOuser();
				vOuser.setCode( resultSet.getInt(1) );
				vOuser.setFirstName( resultSet.getString(2) );
				vOuser.setLastName( resultSet.getString(3) );
				vOuser.setIdentification( resultSet.getInt(4) );
				vOuser.setAddress( resultSet.getString(5) );
				vOuser.setCity( resultSet.getInt(6) );
				vOuser.setTelephone( resultSet.getString(7) );
				vOuser.setUserName( resultSet.getString(8) );
				vOuser.setUserType( resultSet.getInt(9) );

				result.add( vOuser );
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				resultSet.close();
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return result;
	}

	public VOuser searchUserByCode(VOuser vOuser) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Connection connection = dbManager.getConnection();

		VOuser userSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,firstName,lastName,identification,address,city,telephone,userName,userType FROM users WHERE code=?");

			preparedStatement.setInt(1, vOuser.getCode());

			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next())
			{
				userSearch = new VOuser();
				userSearch.setCode( resultSet.getInt(1) );
				userSearch.setFirstName( resultSet.getString(2) );
				userSearch.setLastName( resultSet.getString(3) );
				userSearch.setIdentification( resultSet.getInt(4) );
				userSearch.setAddress( resultSet.getString(5) );
				userSearch.setCity( resultSet.getInt(6) );
				userSearch.setTelephone( resultSet.getString(7) );
				userSearch.setUserName( resultSet.getString(8) );
				userSearch.setUserType( resultSet.getInt(9) );
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return userSearch;
	}

	public VOuser searchUserByUserName(VOuser vOuser) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Connection connection = dbManager.getConnection();

		VOuser userSearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,firstName,lastName,identification,address,city,telephone,userName,userType FROM users WHERE firstName=?");

			preparedStatement.setString(1, vOuser.getFirstName());

			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next())
			{
				userSearch = new VOuser();
				userSearch.setCode( resultSet.getInt(1) );
				userSearch.setFirstName( resultSet.getString(2) );
				userSearch.setLastName( resultSet.getString(3) );
				userSearch.setIdentification( resultSet.getInt(4) );
				userSearch.setAddress( resultSet.getString(5) );
				userSearch.setCity( resultSet.getInt(6) );
				userSearch.setTelephone( resultSet.getString(7) );
				userSearch.setUserName( resultSet.getString(8) );
				userSearch.setUserType( resultSet.getInt(9) );
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return userSearch;
	}
	
	public VOuser authenticUser(VOuser vOuser) 
	{
		PreparedStatement preparedStatement = null;
		VOuser resul = null;
		ResultSet resultSet = null;
		Connection connection = dbManager.getConnection();
		
		try
		{
			preparedStatement = connection.prepareStatement("SELECT code FROM users WHERE userName=? AND password=?");
			
			preparedStatement.setString(1, vOuser.getUserName());
			preparedStatement.setString(2, vOuser.getPassword());
			
			resultSet = preparedStatement.executeQuery();
			
			if(resultSet.next())
			{
				resul = new VOuser();
				resul.setCode( resultSet.getInt(1) );
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return resul;
	}
}
